package com.librelato.persistencia;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.librelato.bean.EmpresaBean;
import com.librelato.bean.HistoricoSolicitacaoProducaoBean;
import com.librelato.bean.ProdutoBean;
import com.librelato.bean.SolicitacaoProducaoBean;
import com.librelato.bean.StatusAcompanhamentoBean;
import com.librelato.persistencia.config.SQLConfiguracao;
import com.librelato.persistencia.enums.EnumCondicao;
import com.librelato.persistencia.interfaces.ISolicitacaoProducaoDao;
import com.librelato.util.Ferramentas;

public class SolicitacaoProducaoDao<P extends SolicitacaoProducaoBean> extends
		DaoGenerico<P> implements ISolicitacaoProducaoDao<P> {

	@Override
	public String procuraPorParam(String tabela, String campo, String valor,
			EnumCondicao condicao) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public P salvar(P p) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void excluir(P p) throws Exception {
		// TODO Auto-generated method stub

	}

	@Override
	public void editar(P p) throws Exception {
		// TODO Auto-generated method stub

	}

	@Override
	public void preSalvar(P p) {
		// TODO Auto-generated method stub

	}

	public List<SolicitacaoProducaoBean> buscaSolicitacoesProducao(
			Map<String, String> param) throws Exception {

		SolicitacaoProducaoBean solicitacao = null;
		List<SolicitacaoProducaoBean> solicitacoes = new ArrayList<SolicitacaoProducaoBean>();
		int psIndex = 1;
		try {
			conn = GerenciadorConexao.getConexao();
			StringBuilder query = new StringBuilder(SQLConfiguracao
					.getInstance().getValor("localiza_solicitacao_producao"));
			if (param.get("FILIAL") != null && !"".equals(param.get("FILIAL"))) {
				if ("ORIGEM".equals(param.get("OPCAOFILIAL"))) {
					query.append(" AND ZZ9_FILORI = ?");
				} else {
					query.append(" AND ZZ9_FILIAL = ?");
				}
			}
			if (param.get("DATADE") != null && !"".equals(param.get("DATADE"))) {
				query.append(" AND ZZ9_DATPRE >= ?");
			}
			if (param.get("DATAATE") != null
					&& !"".equals(param.get("DATAATE"))) {
				query.append(" AND ZZ9_DATPRE <= ?");
			}
			if (param.get("NUMDE") != null && !"".equals(param.get("NUMDE"))) {
				query.append(" AND ZZ9_NUMSP >= ?");
			}
			if (param.get("NUMATE") != null && !"".equals(param.get("NUMATE"))) {
				query.append(" AND ZZ9_NUMSP <= ?");
			}

			if (param.get("LINHA") != null && !"".equals(param.get("LINHA"))) {
				if ("I1".equals(param.get("LINHA"))) {
					query.append(" AND BM_GRUREL IN ('2000','I1')");
				} else if ("I2".equals(param.get("LINHA"))) {
					query.append(" AND BM_GRUREL IN ('1000','I2')");
				}
			}

			if (param.get("STATUS") != null && !"".equals(param.get("STATUS"))) {
				query.append(" AND ZZ9_STATUS = ?");
			}

			query.append(" LEFT JOIN ZPP010 ON ZPP_FILORI = ZZ9_FILORI AND ZPP010.D_E_L_E_T_ <> '*' AND ZPP_NUMERO = ZZ9_NUMSP AND ZPP_TIPO = 'SP'");

			ps = conn.prepareStatement(query.toString());

			if (param.get("FILIAL") != null && !"".equals(param.get("FILIAL"))) {
				ps.setString(psIndex++, param.get("FILIAL"));
			}
			if (param.get("DATADE") != null && !"".equals(param.get("DATADE"))) {
				ps.setString(
						psIndex++,
						Ferramentas.formataData(
								Ferramentas.formataData(param.get("DATADE"),
										"dd/MM/yy").getTime(), "yyyyMMdd"));
			}
			if (param.get("DATAATE") != null
					&& !"".equals(param.get("DATAATE"))) {
				ps.setString(
						psIndex++,
						Ferramentas.formataData(
								Ferramentas.formataData(param.get("DATAATE"),
										"dd/MM/yy").getTime(), "yyyyMMdd"));
			}
			if (param.get("NUMDE") != null && !"".equals(param.get("NUMDE"))) {
				ps.setString(psIndex++, param.get("NUMDE"));
			}
			if (param.get("NUMATE") != null && !"".equals(param.get("NUMATE"))) {
				ps.setString(psIndex++, param.get("NUMATE"));
			}

			if (param.get("STATUS") != null && !"".equals(param.get("STATUS"))) {
				ps.setString(psIndex++, param.get("STATUS"));
			}

			rs = ps.executeQuery();

			while (rs.next()) {
				solicitacao = new SolicitacaoProducaoBean();
				solicitacao.setDataEmissao(Ferramentas.formataData(
						rs.getString("ZZ9_DATASP"), "yyyyMMdd"));
				solicitacao.setDataEntrega(Ferramentas.formataData(
						rs.getString("ZZ9_DATPRE"), "yyyyMMdd"));
				solicitacao.setEmpresaOrigem(new EmpresaBean(rs
						.getString("ZZ9_FILORI"), ""));
				solicitacao.setEmpresaProd(new EmpresaBean(rs
						.getString("ZZ9_FILIAL"), ""));
				solicitacao.setNumeroPV(rs.getString("ZZ9_NUMPV").trim());
				solicitacao.setNumeroSp(rs.getString("ZZ9_NUMSP"));
				solicitacao.setOrigemCriacao(rs.getString("ZZ9_ORIGEM"));
				solicitacao.setProduto(new ProdutoBean(rs.getString(
						"ZZ9_PRODUT").trim(), rs.getString("B1_DESC").trim(),
						rs.getString("B1_UM")));
				solicitacao.setQtdOrigem(rs.getDouble("ZZ9_QUANT"));
				solicitacao.setQtdProduzida(rs.getDouble("ZZ9_QTDPRO"));
				solicitacao.setQtdEnviada(rs.getDouble("ZZ9_QTDENV"));
				solicitacao.setSolicitante(rs.getString("ZZ9_SOLICI"));
				solicitacao.setStatusSp(new StatusAcompanhamentoBean(rs
						.getString("ZZ9_STATUS"), rs.getString("X5_DESCRI")));
				if (rs.getString("ZPP_DATAC") != null) {
					solicitacao.setDataProgramacao(Ferramentas.formataData(
							rs.getString("ZPP_DATAC"), "yyyyMMdd"));
				}

				solicitacoes.add(solicitacao);
			}

		} catch (Exception e) {
			throw e;
		}
		query = null;
		return solicitacoes;

	}

	public List<HistoricoSolicitacaoProducaoBean> buscaHistoricoSolicitacaoProducao(
			SolicitacaoProducaoBean solicitacao) throws Exception {
		List<HistoricoSolicitacaoProducaoBean> hists = new ArrayList<HistoricoSolicitacaoProducaoBean>();
		HistoricoSolicitacaoProducaoBean hist = null;
		try {
			query = SQLConfiguracao.getInstance().getValor(
					"localiza_historico_solicitacao_prod");
			conn = GerenciadorConexao.getConexao();
			ps = conn.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			ps.setString(1, solicitacao.getEmpresaProd().getFilial());
			ps.setString(2, solicitacao.getNumeroSp());
			rs = ps.executeQuery();

			while (rs.next()) {
				hist = new HistoricoSolicitacaoProducaoBean();
				hist.setCodigo(rs.getLong("IDACOMPANHA_SP"));
				hist.setFilOri(rs.getString("FILIALORI_SP"));
				hist.setFilProd(rs.getString("FILIAL_SP"));
				hist.setDataAcompanhamento(rs.getTimestamp("DATAEMISSAO_SP"));
				hist.setNumSp(rs.getString("NUMSP_SP"));
				hist.setObservacao(rs.getString("OBSERVACAO_SP"));
				hist.setStatus(new StatusAcompanhamentoBean(rs
						.getString("STATUS_SP"), rs.getString("X5_DESCRI")));
				hist.setUsuario(rs.getString("USUARIO_SP"));
				hist.setVisualizaExt(rs.getString("VISUALIZA_SP"));
				hists.add(hist);
			}

		} catch (Exception e) {
			throw e;
		}
		return hists;
	}

	public List<StatusAcompanhamentoBean> buscaStatusCadastrados()
			throws Exception {
		List<StatusAcompanhamentoBean> listaStatus = new ArrayList<StatusAcompanhamentoBean>();
		StatusAcompanhamentoBean status = null;
		try {
			conn = GerenciadorConexao.getConexao();
			StringBuilder query = new StringBuilder(SQLConfiguracao
					.getInstance().getValor("localiza_status_solicitacao_prod"));
			rs = conn.prepareCall(query.toString()).executeQuery();
			while (rs.next()) {
				status = new StatusAcompanhamentoBean();
				status.setCodigo(rs.getString("X5_CHAVE").trim());
				status.setDescricao(rs.getString("X5_DESCRI").trim());
				listaStatus.add(status);
			}
		} catch (Exception e) {
			throw e;
		}

		return listaStatus;

	}

}
